/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package fr.cggveritas.crewintranet.migrationdb.access;

import fr.cggveritas.crewintranet.jms.utils.Helpers;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.faces.application.FacesMessage;

/**
 *
 * @author Alassane
 */
public class ConnexionDBAccess {

    ConnexionSGDBMS connexionSGDBMSAccess;

    public ConnexionDBAccess() {
        connexionSGDBMSAccess = new ConnexionSGDBMS();
    }

    public List<PersonnelDataAccess> getListDesPersonnelDeLaBaseAccess(ConnexionSGDBMS connexionSGDBMS) {

        Connection con = null;
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con = DriverManager.getConnection(connexionSGDBMS.getUrl(), connexionSGDBMS.getUserName(), connexionSGDBMS.getPassword());
            Statement test = con.createStatement();
            test.execute("select * from PERSONNEL ");
            ResultSet res = test.getResultSet();
            List p1 = new ArrayList<PersonnelDataAccess>();
            PersonnelDataAccess p = new PersonnelDataAccess();
            if (res != null) {
                String GS = null;
                String fr = null;
                int i = 0;
                while (res.next()) {

                    String tmp = res.getString("Groupe_Sanguin");

                    if (tmp != null) {
                        GS = Helpers.separateur(tmp, "[a-zA-Z]{0,3}");
                        GS = GS.toUpperCase().replaceAll(" ", "");
                        fr = Helpers.separateur(tmp, "[+-]");
                        fr = fr.toUpperCase().replaceAll(" ", "");
                    } else {
                        GS = "";
                        fr = "";
                    }
                    
                    p = new PersonnelDataAccess(
                            res.getString("ID_Personnel"),
                            res.getString("Forname"),
                            res.getString("Surname"),
                            res.getDate("Birth_Date"),
                            res.getString("ID_CARD"),
                            res.getString("Adresse"),
                            GS,
                            fr,
                            res.getDate("Employment_Date"),
                            res.getString("Comments"),
                            res.getString("Place_of_Birth"),
                            res.getString("Village"),
                            res.getString("Daira"),
                            res.getBytes("Photo")
                    );
                    p1.add(p);
                    p = new PersonnelDataAccess();
                    
                }
            }
            
            con.close();
            return p1;
            
        } catch (Exception e) {
            System.out.println("Exception :" + e.getMessage());
        }
        return null;
    }

    public ConnexionSGDBMS getConnexionSGDBMSAccess() {
        return connexionSGDBMSAccess;
    }

    public void setConnexionSGDBMSAccess(ConnexionSGDBMS connexionSGDBMSAccess) {
        this.connexionSGDBMSAccess = connexionSGDBMSAccess;
    }

}
